/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package insert;

/**
 *
 * @author geidy.asprilla
 */
import conexiones.ConectPSQL;
import conexiones.ConectSQLServer;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

/**
 *
 * @author GJ_ASPRILLA
 */
public class InsertFromSQLServer2Postgresql {

    private static ConectSQLServer conn = ConectSQLServer.getInstance();
    private static ConectPSQL connpsql = ConectPSQL.getInstance();
    public static void main(String[] args) {
        
        
        
        try {

            Statement statementSQL;
            Statement statementPSQL;
            
            
            ResultSet rsSql;
            ResultSet rsPsql;
            
            
            
            statementSQL = conn.getConection().createStatement();
            //statementSQL.setFetchSize(50);

            String QueryStringSQL = "SELECT [fecha_ReporteRegional] ,[codigoRegion_ReporteRegional],[climaPrevisto_ReporteRegional],[alertasDiarias_ReporteRegional],[incidenciaAgricultura_ReporteRegional] FROM [AgronetWeb].[dbo].[clima_ReporteRegional] where [fecha_ReporteRegional]= (SELECT max([fecha_ReporteRegional]) FROM [AgronetWeb].[dbo].[clima_ReporteRegional] );";
            rsSql = statementSQL.executeQuery(QueryStringSQL);
            statementPSQL = connpsql.getConection().createStatement();
                   if (rsSql.getConcurrency() == ResultSet.CONCUR_READ_ONLY) {
            System.out.println("ResultSet sql non-updatable.");
            } else {
            System.out.println("ResultSet sql updatable.");
             }   
                     
                   
                   
                   
                   while (rsSql.next()) {
                       //System.out.println("--Alertas Diarias"+"\n"+"INSERT INTO clima.condicionclimatologicadia( region, fecha, textoalerta) VALUES ("+rsSql.getString(2).replaceAll("\\<.*?\\>", "").replaceAll("&nbsp;", "").replace("\n\n", "\r\n")  +", '" + rsSql.getDate(1) +"','"+rsSql.getString(4).replaceAll("\\<.*?\\>", "").replaceAll("&nbsp;", "") +"');");  
                       System.out.println("INSERT INTO clima.condicionclimatologicames(region, fecha, textoclimaprevisto, textoincidencia) VALUES ("+rsSql.getString(2).replaceAll("\\<.*?\\>", "").replaceAll("&nbsp;", "")  +", '" + rsSql.getDate(1) + "','"+ rsSql.getString(3).replaceAll("\\<.*?\\>", "").replaceAll("&nbsp;", "").replace("\r\n\r\n", "\r\n") + "','" + rsSql.getString(5).replaceAll("\\<.*?\\>", "").replaceAll("&nbsp;", "").replace("\r\n\r\n", "\r\n") +"');"+ "\n");
                    
                     String QueryStringPSQLAlertas="--Alertas Diarias"+"\n"+"INSERT INTO clima.condicionclimatologicadia( region, fecha, textoalerta) VALUES ("+rsSql.getString(2).replaceAll("\\<.*?\\>", "").replaceAll("&nbsp;", "")  +", '" + rsSql.getDate(1) +"','"+rsSql.getString(4).replaceAll("\\<.*?\\>", "").replaceAll("&nbsp;", "").replace("\n\n", "\r\n") +"');"+ "\n";
                     String QueryStringPSQLClimaPrevisto=("INSERT INTO clima.condicionclimatologicames(region, fecha, textoclimaprevisto, textoincidencia) VALUES ("+rsSql.getString(2).replaceAll("\\<.*?\\>", "").replaceAll("&nbsp;", "")  +", '" + rsSql.getDate(1) + "','"+ rsSql.getString(3).replaceAll("\\<.*?\\>", "").replaceAll("&nbsp;", "").replace("\r\n\r\n", "\r\n") + "','" + rsSql.getString(5).replaceAll("\\<.*?\\>", "").replaceAll("&nbsp;", "").replace("\r\n\r\n", "\r\n") +"');"+ "\n");
                      
                    statementPSQL.addBatch(QueryStringPSQLAlertas);
                    statementPSQL.addBatch(QueryStringPSQLClimaPrevisto);
                   
                   }
                   statementPSQL.executeBatch();
                   
                    /* 
                    * //System.out.println("INSERT INTO clima.condicionclimatologicames(region, fecha, textoclimaprevisto, textoincidencia) VALUES ("+rsSql.getString(2).replaceAll("\\<.*?\\>", "").replaceAll("&nbsp;", "")  +", '" + rsSql.getDate(1) + "','"+ rsSql.getString(3).replaceAll("\\<.*?\\>", "").replaceAll("&nbsp;", "") + "','" + rsSql.getString(5).replaceAll("\\<.*?\\>", "").replaceAll("&nbsp;", "") +"');"+ "\n");
                    * 
                    * //String QueryStringPSQLAlertas="--Alertas Diarias"+"\n"+"INSERT INTO clima.condicionclimatologicadia( region, fecha, textoalerta) VALUES ("+rsSql.getString(2).replaceAll("\\<.*?\\>", "").replaceAll("&nbsp;", "").replace("\n\n", "\r\n")  +", '" + rsSql.getDate(1) +"','"+rsSql.getString(4).replaceAll("\\<.*?\\>", "").replaceAll("&nbsp;", "").replace("\n\n", "\r\n") +"');"+ "\n";
                    * //String QueryStringPSQL = "--Alertas Diarias"+"\n"+"INSERT INTO clima.condicionclimatologicadia( region, fecha, textoalerta) VALUES ("+rsSql.getString(2).replaceAll("\\<.*?\\>", "").replaceAll("&nbsp;", "").replace("\n\n", "\r\n")  +", '" + rsSql.getDate(1) +"','"+rsSql.getString(4).replaceAll("\\<.*?\\>", "").replaceAll("&nbsp;", "").replace("\n\n", "\r\n") +"');"+ "\n";
                    * rsPsql = statementPSQL.executeQuery("--Alertas Diarias"+"\n"+"INSERT INTO clima.condicionclimatologicadia( region, fecha, textoalerta) VALUES ("+rsSql.getString(2).replaceAll("\\<.*?\\>", "").replaceAll("&nbsp;", "").replace("\n\n", "\r\n")  +", '" + rsSql.getDate(1) +"','"+rsSql.getString(4).replaceAll("\\<.*?\\>", "").replaceAll("&nbsp;", "").replace("\n\n", "\r\n") +"');");
                    * System.out.println("--Alertas Diarias"+"\n"+"INSERT INTO clima.condicionclimatologicadia( region, fecha, textoalerta) VALUES ("+rsSql.getString(2).replaceAll("\\<.*?\\>", "").replaceAll("&nbsp;", "").replace("\n\n", "\r\n")  +", '" + rsSql.getDate(1) +"','"+rsSql.getString(4).replaceAll("\\<.*?\\>", "").replaceAll("&nbsp;", "").replace("\n\n", "\r\n") +"');");
                    * /*if (rsPsql.getType() == ResultSet.TYPE_FORWARD_ONLY) {
                    * System.out.println("ResultSet non-scrollable.");
                    * } else {
                    * System.out.println("ResultSet scrollable.");
                    * }  */
            
            
            
            
 
               //System.out.println(QueryStringPSQLIncidencia);
                
               /* while (rsPsql.next()) {
                System.out.println(QueryStringPSQL);
                System.out.println("fecha de hoy "+ getCurrentTimeStamp());
                }*/
            
            
            
            
    
              
            
        } catch (Exception ex) {
            System.out.println("No es posible conectar con la base de datos ");
            System.out.println(ex.getMessage());
            
            
            
        }
    }
    private static final DateFormat dateFormat = new SimpleDateFormat(
			"yyyy-MM-dd");
    private static String getCurrentTimeStamp() {
 
		java.util.Date today = new java.util.Date();
		return dateFormat.format(today.getTime());
 
	}
}
